/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.googlecode.datawander.shemareader;


import com.googlecode.datawander.connectors.MyRelationalDataBaseException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.googlecode.datawander.connectors.OracleConnector;
import com.googlecode.datawander.i18n.I18n;
import java.sql.Connection;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleResultSet;
import oracle.jdbc.OracleResultSetMetaData;
import oracle.jdbc.OracleTypes;
import org.apache.log4j.Logger;

/**
 *
 * @author Marcin Stachniuk
 */
public class OracleDatabaseSchemaReader {

    private static final Logger logger = Logger.getLogger(OracleDatabaseSchemaReader.class);

    /**
     * String use to insert into querys before and after table and field names.
     */
    public static final String ESC_CHAR = "\"";

    private List<MetaClass> metaClassList = new ArrayList<MetaClass>();

    /**
     * Reads from relational database, how tables have user and transformate
     * every table on MetaClass object.
     * @throws MyRelationalDataBaseException If it's problem with execute
     * SQL query or it's problem with database connection.
     */
    public void readDatabaseSchema() throws MyRelationalDataBaseException {
        logger.trace("readDatabaseSchema()");
        Connection conn = OracleConnector.getConnection();
        String message = I18n.getString("getconnectionobject");
        message = String.format(message, conn);
        logger.debug(message);

        try {
            PreparedStatement pstmt = conn.prepareStatement(
                    "select OBJECT_NAME from user_objects where object_type = 'TABLE'");
            OracleResultSet resultSet = (OracleResultSet) pstmt.executeQuery();

            logger.info(I18n.getString("igetdeletedtables"));
            List<String> dropedTables = loadListOfDroppedTables();

            logger.info(I18n.getString("tables"));

            while(resultSet.next()) {
                String className = (String)resultSet.getObject("OBJECT_NAME"); //getString("OBJECT_NAME");
                message = I18n.getString("classname");
                message = String.format(message, className);
                logger.info(message);

                if(dropedTables.contains(className)) {
                    message = I18n.getString("thistableisdeletedtablenocreateklass");
                    message = String.format(message, className);
                    logger.info(message);
                } else {
                    MetaClass result = createTableClassFromMetaClass(conn, className);
                    metaClassList.add(result);
                }
            }
        } catch(SQLException e) {
            logger.fatal(e,e);
            throw new MyRelationalDataBaseException(e.getMessage());
        }
    }

    /**
     * Reads shema of tableName and create MetaClass
     * @param tableName Name of table.
     * @throws MyRelationalDataBaseException
     */
    private MetaClass createTableClassFromMetaClass(Connection conn, String tableName)
            throws MyRelationalDataBaseException  {
        logger.trace("createTableClassFromMetaClass("+tableName+")");

        MetaClass metaClass = null;

        try {
            OraclePreparedStatement pstmt = (OraclePreparedStatement) conn.prepareStatement(
                    "select * FROM " + ESC_CHAR + tableName + ESC_CHAR);
            OracleResultSet resultSet = (OracleResultSet) pstmt.executeQuery();

            // create MetaClass object
            metaClass = new MetaClass();
            metaClass.setClassName(tableName);

            OracleResultSetMetaData resultMetaData = 
                    (OracleResultSetMetaData) resultSet.getMetaData();
            String message = I18n.getString("numberofreturnedcolumns");
            message = String.format(message, resultMetaData.getColumnCount());
            logger.info(message);
            for(int i=1; i <= resultMetaData.getColumnCount(); i++) {
                logger.info("CatalogName: "+resultMetaData.getCatalogName(i)+
                        " ColumnClassName: "+resultMetaData.getColumnClassName(i)+
                        " ColumnLabel: "+resultMetaData.getColumnLabel(i)+
                        " ColumnName: "+resultMetaData.getColumnName(i)+
                        " ColumnType: "+resultMetaData.getColumnType(i)+
                        " ColumnTypeName: "+resultMetaData.getColumnTypeName(i));

                String fieldClassName = generateFieldClassName(resultMetaData, i);

                MetaClassField field = new MetaClassField();
                field.setType(fieldClassName);
                field.setValueName(resultMetaData.getColumnName(i));

                readFieldDetails(conn, tableName, field);

                metaClass.addField(field);

                //resultMetaData.
            }
        } catch (SQLException e) {
            logger.fatal(e,e);
            throw new MyRelationalDataBaseException(e.getMessage());
        }

        return metaClass;
    }

    /**
     * Show information about database shem from meta class's.
     */
    public void showInfoFromMetaClas() {
        logger.trace("showInfoFromMetaClas");

        logger.info("Liczba metaClass: "+metaClassList.size());

        for (MetaClass mc : metaClassList) {
            String message = I18n.getString("classname");
            message = String.format(message, mc.getClassName());
            logger.info(message);
            logger.info(I18n.getString("types"));
            for(int i=0; i < mc.getFieldsNumber(); i++) {
                MetaClassField field = mc.getField(i);
                logger.info(field.getType()+" "+field.getValueName() + " PK: " +
                        field.isPrimaryKey() + " FK: " + field.isForeignKey() +
                        (field.isForeignKey() ? " to "+field.getForeignFieldTable() + "."+field.getForeignFieldColumn() : "") +
                        (field.isPrimaryKey() || field.isForeignKey() ? " unique: "+field.isUnique() : ""));
            }
        }
    }

    /**
     * Gets list deleted table's
     * @return List of names of deleted table's.
     * @throws MyRelationalDataBaseException if is impossible to gets info about
     * deleted table's.
     */
    private List<String> loadListOfDroppedTables() throws MyRelationalDataBaseException  {
        logger.trace("loadListOfDroppedTables()");

        List<String> list = new ArrayList<String>();

        Connection conn = OracleConnector.getConnection();

        try {
            PreparedStatement pstmt = conn.prepareStatement("select OBJECT_NAME from recyclebin where type = 'TABLE'");
            OracleResultSet resultSet = (OracleResultSet) pstmt.executeQuery();

            while(resultSet.next()) {
                String message = I18n.getString("deletedtable");
                message = String.format(message, resultSet.getString("OBJECT_NAME"));
                logger.info(message);
                list.add(resultSet.getString("OBJECT_NAME"));
            }
        } catch(SQLException e) {
            logger.fatal(e,e);
            throw new MyRelationalDataBaseException(e.getMessage());
        }

        return list;
    }

    /**
     * Return list of MetaClass
     * @return
     */
    public List<MetaClass> getMetaClassList() {
        return metaClassList;
    }

    /**
     * Generuje typ kolumny jaki powinna mieć.
     * @param resultMetaData
     * @param i Numer kolumny.
     * @return Typ kolumny
     * @throws SQLException Jeśli błąd.
     */
    private String generateFieldClassName(OracleResultSetMetaData resultMetaData, int i) throws SQLException {
        logger.trace("generateFieldClassName("+resultMetaData+", "+i+")");

        String result = resultMetaData.getColumnClassName(i);

        return result;
    }

    /**
     * Method return tab with all names of classes.
     * @return
     */
    public String [] getTabOfAllClassNames() {
        int size = metaClassList.size();
        String [] result = new String [size];

        int i=0;
        for(MetaClass meta : metaClassList) {
            result[i] = meta.getClassName();
            i++;
        }

        return result;
    }


    private void readFieldDetails(Connection conn, String tableName, MetaClassField field)
            throws MyRelationalDataBaseException {

        try {
            // read primary key
            OraclePreparedStatement pstmt = (OraclePreparedStatement) conn.prepareStatement(
                    "SELECT cols.column_name FROM user_constraints cons, user_cons_columns cols WHERE cols.table_name = '" +
                    tableName + "' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name " +
                    "AND cons.owner = cols.owner AND cols.column_name = '"+field.getValueName()+"'");
            OracleResultSet resultSet = (OracleResultSet) pstmt.executeQuery();

            if(resultSet.next()) {
                field.setPrimaryKey(true);

                logger.trace("Find primary key: "+tableName+"."+field.getValueName());

                String sql = "SELECT cons.CONSTRAINT_NAME, C.TABLE_NAME, C.COLUMN_NAME " +
                        "FROM user_constraints cons, user_cons_columns cols, user_cons_columns C "+
                        "WHERE cols.table_name = '" + tableName + "' AND cons.constraint_type = 'R' "+
                        "AND cons.R_constraint_name = cols.constraint_name " +
                        "AND cons.owner = cols.owner AND cols.column_name = '" + field.getValueName() +
                        "' AND cons.CONSTRAINT_NAME = C.CONSTRAINT_NAME";

                // read referential integrity for this column
                pstmt = (OraclePreparedStatement) conn.prepareStatement(sql);
                resultSet = (OracleResultSet) pstmt.executeQuery();

                logger.trace(sql);
                

                List<String> tableNames = new ArrayList<String>();
                List<String> columnsNames = new ArrayList<String>();
                List<String> referenceNames = new ArrayList<String>();
                while (resultSet.next()) {
                    tableNames.add(resultSet.getString("TABLE_NAME"));
                    columnsNames.add(resultSet.getString("COLUMN_NAME"));
                    referenceNames.add(resultSet.getString("CONSTRAINT_NAME"));
                }

                field.setPrimaryKeyReferenceTable(tableNames.toArray(new String[]{}));
                field.setPrimaryKeyReferenceColumn(columnsNames.toArray(new String[]{}));
                field.setPrimaryKeyReferenceName(referenceNames.toArray(new String[]{}));
            }

            // read foreign key
            String sql = "select A.CONSTRAINT_NAME, B.TABLE_NAME, B.COLUMN_NAME "+
                    "from USER_CONSTRAINTS A, USER_CONS_COLUMNS B, USER_CONS_COLUMNS C " +
                    "where A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME And A.CONSTRAINT_TYPE = 'R' " +
                    "and A.TABLE_NAME='" + tableName + "' and C.COLUMN_NAME='" + field.getValueName() + "' "+
                    "and C.CONSTRAINT_NAME = A.CONSTRAINT_NAME and A.TABLE_NAME = C.TABLE_NAME";

            pstmt = (OraclePreparedStatement) conn.prepareStatement(sql);

            resultSet = (OracleResultSet) pstmt.executeQuery();

            if(resultSet.next()) {
                field.setForeignKey(true);

                logger.trace("Find foreign key: "+tableName+"."+field.getValueName());
                logger.trace(sql);

                logger.trace("Tablename "+resultSet.getString("TABLE_NAME")+" columnname: " +
                        resultSet.getString("COLUMN_NAME")+" ref name: "+resultSet.getString("CONSTRAINT_NAME"));
                field.setForeignFieldTable(resultSet.getString("TABLE_NAME"));
                field.setForeignFieldColumn(resultSet.getString("COLUMN_NAME"));
                field.setForeignFieldReferenceName(resultSet.getString("CONSTRAINT_NAME"));
            }

            // read unique value
            pstmt = (OraclePreparedStatement) conn.prepareStatement(
                    "select A.CONSTRAINT_NAME from USER_CONSTRAINTS A, USER_CONS_COLUMNS B " +
                    "where A.TABLE_NAME='" + tableName + "' and B.COLUMN_NAME='" + field.getValueName() +
                    "' and A.CONSTRAINT_NAME = B.CONSTRAINT_NAME and A.CONSTRAINT_TYPE='U'");

            resultSet = (OracleResultSet) pstmt.executeQuery();

            if(resultSet.next()) {
                field.setUnique(true);
            }

        } catch(SQLException e) {
            logger.fatal(e,e);
            throw new MyRelationalDataBaseException(e.getMessage());
        }
    }

}
